package com.ejoysoft.wlanyz.business;

import com.ejoysoft.common.DbConnect;
import com.ejoysoft.common.Globa;
import com.sun.crypto.provider.RSACipher;

import java.io.IOException;
import java.nio.charset.Charset;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Vector;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequestWrapper;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.jsp.JspWriter;
import javax.servlet.jsp.PageContext;


public class WarnAppear {

    private Globa globa;
    private DbConnect db;

    //构造方法
    public WarnAppear() {
    }

    public WarnAppear(Globa globa) {
        this.globa = globa;
        db = globa.db;
    }

    //构造方法 
    public WarnAppear(Globa globa, boolean b) {
        this.globa = globa;
        db = globa.db;
        if (b) globa.setDynamicProperty(this);
    }
    
    private String strTableName = "wlan_warnings";
    private String strTableName2 = "WLAN_RESC_COMUNITY";
    private String strTableName3 = "wlan_test_result";
    
    //列表记录集
    public WarnAppear load(ResultSet rs ) throws SQLException{
    	WarnAppear theBean = new WarnAppear();
        try {
            theBean.setStrId(rs.getString("strId"));
            theBean.setStrTaskId(rs.getString("strTaskId"));
            theBean.setStrTaskName(rs.getString("strTaskName"));
            theBean.setStrCity(rs.getString("strCity"));
            theBean.setStrTown(rs.getString("strTown"));
            theBean.setStrApName(rs.getString("strApName"));
            theBean.setStrMac(rs.getString("strMac"));
            theBean.setStrCommunityId(rs.getString("strCommunityId"));
            theBean.setStrIndicatorName(rs.getString("strIndicatorName"));
            theBean.setIntIndicatorValue(rs.getInt("intIndicatorValue"));
            theBean.setIntIndicatorTestValue(rs.getInt("intIndicatorTestValue"));
            
        } catch (Exception e) {
            e.printStackTrace();
        }
        return theBean;
    }
    //查询符合条件的记录�?�数
    public int getCount(String where,String strStartTime ,String strEndTime ) throws SQLException{
        int count = 0;
        if(strStartTime.equals(""))strStartTime = "1000-1-01";
        if(strEndTime.equals(""))strEndTime = "9999-12-30";
        String timeWhere = " and b.dtcreatdate between to_date('" + strStartTime + "','yyyy-MM-dd hh24.mi.ss') and to_date('" + strEndTime + "','yyyy-MM-dd hh24.mi.ss')" ; 
        try {
            String sql = "SELECT count(*) FROM " + strTableName + " a ,"+strTableName3+" b " ;
                sql = String.valueOf(sql) + String.valueOf(where) + String.valueOf(timeWhere);
                System.err.println(sql+"lllllllllllllllllllllllll");
            ResultSet rs = db.executeQuery(sql);
            if (rs.next())
                count = rs.getInt(1);
            rs.close();
            return count;
        } catch (Exception ee) {
            ee.printStackTrace();
            return count;
        }
    }
    
  
    
  //列表记录
    public Vector<WarnAppear> list(String where, int startRow, int rowCount ,String strStartTime ,String strEndTime) throws SQLException{
        Vector<WarnAppear> beans = new Vector<WarnAppear>();
        try {
        	 if(strStartTime.equals(""))strStartTime = "1000-1-01";
             if(strEndTime.equals(""))strEndTime = "9999-12-30";
             
             String timeWhere = " and b.dtcreatdate between to_date('" + strStartTime + "','yyyy-MM-dd hh24.mi.ss') and to_date('" + strEndTime + "','yyyy-MM-dd hh24.mi.ss')" ; 

            String sql = "SELECT a.*  FROM   " + strTableName + " a ,"+strTableName3+" b " ;
            sql = String.valueOf(sql) + String.valueOf(where) + String.valueOf(timeWhere);
            //System.err.println(sql+"lllllllllllllllllllllllll");
            Statement s = db.getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            if (startRow != 0 && rowCount != 0)
                s.setMaxRows((startRow + rowCount) - 1);
            ResultSet rs = s.executeQuery(sql);
            if (rs != null && rs.next()) {
                if (startRow != 0 && rowCount != 0)
                    rs.absolute(startRow);
                do {
                	WarnAppear theBean = new WarnAppear();
                    theBean = load(rs);
                    beans.addElement(theBean);
                } while (rs.next());
            }
            rs.close();
            s.close();
        } catch (Exception ee) {
            ee.printStackTrace();
        }
        return beans;
    } 
    //拿到小区区码的方法
    public String getLac(String strCommId){
    	DbConnect dbConnect = new DbConnect();
    	try {
    		String strLac = "";
			String sql = "select strlac from " + strTableName2 + " where strcommid = '" + strCommId + "'";
 
			ResultSet rs = dbConnect.executeQuery(sql);
			if (rs !=null && rs.next() ) {
				strLac = rs.getString("strlac");
			} 
			
			rs.close();
			return strLac;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
		finally{
			dbConnect.closeCon();
		}
    }
    
  //拿到所有预警的指标名
    public ArrayList<String> getIndicatorName( String where)
    {
    	ArrayList<String>  beans = new ArrayList<String>();
    	String sql = "select distinct(a.strIndicatorName) from "+ strTableName + " a ,"+strTableName3+" b " ;
    	if(where!=null && where.length()>0)
    		sql = sql +" "+where;
    	//System.err.println(sql+"::::::::::::::::::::");
    	ResultSet re = db.executeQuery(sql);
    	try {
    	
			if(re != null && re.next())
			{   //String country = re.getString("");
				do{
					String indicatorName = re.getString("strIndicatorName");
					beans.add(indicatorName);
				}while(re.next());
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return beans;
    }
    
  //拿到所有时间上指标的预警个数
    public int getWarnNum( String time,String name)//传过来时间和指标名
    {
    	int warnNum = 0;
    	if (!time.equals("")) {
			time=time.substring(0, time.lastIndexOf("."));
		}
    	String where = " where a.strIndicatorName = '"+name+"' and b.dtcreatdate = to_date('" + time + "','yyyy-MM-dd hh24.mi.ss')";
    	String sql = "select count(a.strid) from "+ strTableName + " a ,"+strTableName3+" b " ;
    	if(where!=null && where.length()>0)
    		sql = sql +" "+where;
    	ResultSet re = db.executeQuery(sql);
    	try {
    	
			if(re != null && re.next())
			{   //String country = re.getString("");
				do{
					 warnNum = re.getInt(1);
				}while(re.next());
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return warnNum;
    }
    
  //拿到所有需要显示的时间横坐标
    public ArrayList<String> getTestTime( String where)
    {
    	ArrayList<String>  beans = new ArrayList<String>();
    	String sql = "select distinct(b.dtcreatdate) from "+ strTableName + " a ,"+strTableName3+" b " ;
    	if(where!=null && where.length()>0)
    		sql = sql +" "+where;
    	//System.err.println(sql+";;;;;;;;;;;;;;;;;");
    	ResultSet re = db.executeQuery(sql);
    	try {
    	
			if(re != null && re.next())
			{   //String country = re.getString("");
				do{
					String dttesttime = re.getString("dtcreatdate");
					beans.add(dttesttime);
				}while(re.next());
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return beans;
    }
    
    //调出所有地市名称
    public ArrayList<String> getAllCitiesByName( String where)
    {
    	ArrayList<String>  beans = new ArrayList<String>();
    	String sql = "select distinct(strCity) from "+strTableName+" ";
    	if(where!=null && where.length()>0)
    		sql = sql +" "+where;
    	ResultSet re = db.executeQuery(sql);
    	try {
    	
			if(re != null && re.next())
			{   //String country = re.getString("");
				do{
					String city = re.getString("strCity");
					beans.add(city);
				}while(re.next());
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return beans;
    }
    
    private String strId;
    public String getStrId() {
		return strId;
	}

	public void setStrId(String strId) {
		this.strId = strId;
	}

	public String getStrTaskId() {
		return strTaskId;
	}

	public void setStrTaskId(String strTaskId) {
		this.strTaskId = strTaskId;
	}

	public String getStrTaskName() {
		return strTaskName;
	}

	public void setStrTaskName(String strTaskName) {
		this.strTaskName = strTaskName;
	}

	public String getStrNetType() {
		return strNetType;
	}

	public void setStrNetType(String strNetType) {
		this.strNetType = strNetType;
	}

	public String getStrCity() {
		return strCity;
	}

	public void setStrCity(String strCity) {
		this.strCity = strCity;
	}

	public String getStrTown() {
		return strTown;
	}

	public void setStrTown(String strTown) {
		this.strTown = strTown;
	}

	public String getStrApName() {
		return strApName;
	}

	public void setStrApName(String strApName) {
		this.strApName = strApName;
	}

	public String getStrMac() {
		return strMac;
	}

	public void setStrMac(String strMac) {
		this.strMac = strMac;
	}

	public String getStrCommunityId() {
		return strCommunityId;
	}

	public void setStrCommunityId(String strCommunityId) {
		this.strCommunityId = strCommunityId;
	}

	public String getStrIndicatorName() {
		return strIndicatorName;
	}

	public void setStrIndicatorName(String strIndicatorName) {
		this.strIndicatorName = strIndicatorName;
	}


	public int getIntIndicatorValue() {
		return intIndicatorValue;
	}

	public void setIntIndicatorValue(int intIndicatorValue) {
		this.intIndicatorValue = intIndicatorValue;
	}
	
	public int getIntIndicatorTestValue() {
		return intIndicatorTestValue;
	}

	public void setIntIndicatorTestValue(int intIndicatorTestValue) {
		this.intIndicatorTestValue = intIndicatorTestValue;
	}
//	public static void shareMethodForExport(StringBuffer sb,String strName,HttpServletResponse response,JspWriter out,PageContext pageContext){
//   	   	System.out.println(sb);
//	    String strFileName = strName;//自定义xls文件的文件名
//	    response.setCharacterEncoding("utf-8");
//	    response.setContentType("APPLICATION/*;charset=utf-8");
//	    response.setHeader( "Content-Disposition", "attachment;filename="+ new String(strFileName.getBytes(Charset.forName("GBK")), Charset.forName("ISO8859-1")));
//	    out = pageContext.pushBody();
//	    ServletOutputStream output;
//		try {
//			output = response.getOutputStream();
//		    System.out.println(sb.toString());
//		    output.write(sb.toString().getBytes("utf-8"));
//		} catch (IOException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//	}
	private String strTaskId;//任务id
    private String strTaskName;//任务名
    private String strNetType;//网络类型
    private String strCity;//地市
    private String strTown;//区县
    private String strApName;//AP名称
    private String strMac;//Mac地址
    private String strCommunityId;//小区Id
    private String strLac;//小区区码
    private String strIndicatorName;//指标名称
    private int intIndicatorValue;//指标值
    private int intIndicatorTestValue;//指标值
}